package test.testList;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import test.bean.TdaTable;
import test.bean.TdaTableColumn;

import java.io.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class ExcelToTable {
    private static final Logger logger = LoggerFactory.getLogger(ExcelToTable.class);

    public static Map<String, TdaTable> readExcel(String filePath, int startRow, boolean isExcel2003) throws Exception {
        InputStream is = null;
        try {
            /** 创建读取文件的输入流 */
            is = new FileInputStream(new File(filePath));
            /** 根据版本选择创建Workbook的方式 */
            Workbook wb;
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {
                wb = new XSSFWorkbook(is);
            }
            int numberOfSheets = wb.getNumberOfSheets();

            Map<String, TdaTable> map = new LinkedHashMap<>();
            /** 调用本类的读取方法读取excel数据 */
            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheet = wb.getSheetAt(i);
                String sheetName = sheet.getSheetName();
                TdaTable tdaTable = read(wb, startRow, sheet);

                map.put(sheetName,tdaTable);
            }
            return map;
        } catch (Exception ex) {
            logger.error("读取excel文件异常!", ex);
            ex.printStackTrace();
            throw ex;
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static TdaTable read(Workbook wb, int startRow, Sheet sheet) throws Exception {
        /** 总列数 */
        int totalCells = 0;
        /** 创建集合存储读取的数据 */
        List<List<String>> dataLst = new ArrayList<List<String>>();

        /** 得到Excel的行数 */
        int totalRows = sheet.getPhysicalNumberOfRows();
        /** 得到Excel的列数 */
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        TdaTable tdaTable = new TdaTable();

        List<TdaTableColumn> list = new ArrayList<>();

        /** 循环Excel的行 */
        for (int r = startRow; ; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                break;
            }
            Cell cell = row.getCell(2);
            String cellValue = getCellValue(cell);
            switch (r){
                case 0:
                    tdaTable.setTableName(cellValue);
                    break;
                case 1:
                    tdaTable.setFunctionDescription(cellValue);
                    break;
                case 2:
                    String[] split = cellValue.split(",");
                    tdaTable.setTablePrimaryKey(split);
                    break;
                case 3:
                    String[] split1 = cellValue.split(",");
                    tdaTable.setBusinessPrimaryKey(split1);
                    break;
                case 4:
                    tdaTable.setForeignKeyDescription(cellValue);
                    break;
                case 5:
                    tdaTable.setIndexDescription(cellValue);
                    break;
                case 6:
                    String[] split2 = cellValue.split(",");
                    tdaTable.setPartitionField(split2);
                    break;
                case 7:
                    tdaTable.setCollectionMode(cellValue);
                    break;
                case 8:
                    tdaTable.setSourceTableCollectionRules(cellValue);
                    break;
                case 9:
                    tdaTable.setTargetProcessingRules(cellValue);
                    break;
            }
            if(r > 10){
                //当字段编号没有值的时候跳出循环
                Cell cell1 = row.getCell(1);
                String cellValue1 = getCellValue(cell1);
                if(StringUtils.isEmpty(cellValue1)){
                    break;
                }
                TdaTableColumn tdaTableColumn = new TdaTableColumn();
                for (int i = 1; i <12 ; i++) {
                    String columValue = getCellValue(row.getCell(i));
                    switch (i){
                        case 1:
                            tdaTableColumn.setColumnNumber(columValue);
                            break;
                        case 2:
                            tdaTableColumn.setDataTypeAndPrecision(columValue);
                            break;
                        case 3:
                            tdaTableColumn.setNullAble(columValue);
                            break;
                        case 4:
                            tdaTableColumn.setDefaultValue(columValue);
                            break;
                        case 5:
                            tdaTableColumn.setColumnName(columValue);
                            break;
                        case 6:
                            tdaTableColumn.setSourceTableName(columValue);
                            break;
                        case 7:
                            tdaTableColumn.setSourceColumnName(columValue);
                            break;
                        case 8:
                            tdaTableColumn.setTransitionLogic(columValue);
                            break;
                        case 9:
                            tdaTableColumn.setDictionaryComparisonLogic(columValue);
                            break;
                        case 10:
                            tdaTableColumn.setIsCollect(columValue);
                            break;
                        case 11:
                            tdaTableColumn.setRemarks(columValue);
                            break;
                    }
                }
                list.add(tdaTableColumn);
            }
        }
        tdaTable.setTdaTableColumnList(list);

        return tdaTable;
    }

    private static String getCellValue(Cell cell){
        String cellValue = "";
        if (null != cell) {
            // 以下是判断数据的类型
            switch (cell.getCellTypeEnum()) {
                case NUMERIC: // 数字
                    // 判断是不是日期格式
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        cellValue = cell.getDateCellValue() + "";
                    }else {
                        cellValue = cell.getNumericCellValue() + "";
                    }
                    break;
                case STRING: // 字符串
                    cellValue = cell.getStringCellValue();
                    break;
                case BOOLEAN: // Boolean
                    cellValue = cell.getBooleanCellValue() + "";
                    break;
                case FORMULA: // 公式
                    cellValue = cell.getCellFormula() + "";
                    break;
                case BLANK: // 空值
                    cellValue = "";
                    break;
                case ERROR: // 故障
                    cellValue = "非法字符";
                    break;
                default:
                    cellValue = "未知类型";
                    break;
            }
        }
        return cellValue;
    }
}
